import time
import xlrd
from selenium.webdriver.common.by import By
from xlutils.copy import copy

import DBUtils
from selenium import webdriver


class Test_bankcard:
    book = copy(xlrd.open_workbook(filename="a.xls", encoding_override=True))
    time = time.strftime('%y年%m月%d日%H:%M:%S')

    def test_addbank_success001(self):  # 新增银行卡 伊拉克中央银行

        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH, '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)

        element06 = driver.find_element(By.XPATH, '//button[@id="bankCard_add_modal_btn"]')
        element06.click()

        time.sleep(1)
        element07 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @class="form-control" and @id="cardBank_add_input"]')
        element07.send_keys("伊朗中央银行")
        element08 = driver.find_element(By.XPATH, '//span[text()="信用卡"]')
        element08.click()
        element09 = driver.find_element(By.XPATH, '//*[@id="cardNum_add_input"]')
        element09.send_keys("1234567890123456")
        element10 = driver.find_element(By.XPATH, '//*[@id="bankCard_save_btn"]')
        element10.click()
        sql = "select * from bankcard where userID=1"
        if len(DBUtils.select(sql, [])) == 3:
            print("新增成功")
            self.book.get_sheet(3).write(3, 6, "passed")
            self.book.get_sheet(3).write(3, 7, "晏凯旋")
            self.book.get_sheet(3).write(3, 8, self.time)
            self.book.save("b.xls")
        else:
            print("新增失败")
            self.book.get_sheet(3).write(3, 6, "failed")
            self.book.get_sheet(3).write(3, 7, "晏凯旋")
            self.book.get_sheet(3).write(3, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 3

    def test_addbank_failed001(self):  # 错误卡号17位新增

        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH, '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        element06 = driver.find_element(By.XPATH, '//button[@id="bankCard_add_modal_btn"]')
        element06.click()

        time.sleep(1)
        element07 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @class="form-control" and @id="cardBank_add_input"]')
        element07.send_keys("伊拉克中央银行")
        element08 = driver.find_element(By.XPATH, '//span[text()="信用卡"]')
        element08.click()
        element09 = driver.find_element(By.XPATH, '//*[@id="cardNum_add_input"]')
        element09.send_keys("12345678901234569")
        element10 = driver.find_element(By.XPATH, '//*[@id="bankCard_save_btn"]')
        element10.click()
        sql = "select * from bankcard where cardNum=12345678901234569"
        if len(DBUtils.select(sql, [])) == 0:
            print("新增失败")
            self.book.get_sheet(3).write(4, 6, "passed")
            self.book.get_sheet(3).write(4, 7, "晏凯旋")
            self.book.get_sheet(3).write(4, 8, self.time)
            self.book.save("b.xls")
        else:
            print("新增成功")
            self.book.get_sheet(3).write(4, 6, "failed")
            self.book.get_sheet(3).write(4, 7, "晏凯旋")
            self.book.get_sheet(3).write(4, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 0


    def test_addbank_failed002(self):  # 重复卡号新增

        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH, '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        element06 = driver.find_element(By.XPATH, '//button[@id="bankCard_add_modal_btn"]')
        element06.click()

        time.sleep(1)
        element07 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @class="form-control" and @id="cardBank_add_input"]')
        element07.send_keys("伊拉克喜喜银行")
        element08 = driver.find_element(By.XPATH, '//span[text()="信用卡"]')
        element08.click()
        time.sleep(1)
        element09 = driver.find_element(By.XPATH, '//*[@id="cardNum_add_input"]')
        element09.send_keys("1234567890123456")
        element10 = driver.find_element(By.XPATH, '//*[@id="bankCard_save_btn"]')
        element10.click()
        sql = "select * from bankcard where cardBank='伊拉克喜喜银行' and userID=1"
        if len(DBUtils.select(sql, [])) == 1:
            print("新增失败")
            self.book.get_sheet(3).write(5, 6, "passed")
            self.book.get_sheet(3).write(5, 7, "晏凯旋")
            self.book.get_sheet(3).write(5, 8, self.time)
            self.book.save("b.xls")
        else:
            print("新增成功")
            self.book.get_sheet(3).write(5, 6, "failed")
            self.book.get_sheet(3).write(5, 7, "晏凯旋")
            self.book.get_sheet(3).write(5, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 1

    def test_addbank_failed003(self):  # 已有借记卡号 新增相同信用卡号

        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH, '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        element06 = driver.find_element(By.XPATH, '//button[@id="bankCard_add_modal_btn"]')
        element06.click()

        time.sleep(1)
        element07 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @class="form-control" and @id="cardBank_add_input"]')
        element07.send_keys("伊拉克国民银行")
        element08 = driver.find_element(By.XPATH, '//span[text()="信用卡"]')
        element08.click()
        time.sleep(1)
        element09 = driver.find_element(By.XPATH, '//*[@id="cardNum_add_input"]')
        element09.send_keys("0123456789665599")
        time.sleep(1)
        element10 = driver.find_element(By.XPATH, '//*[@id="bankCard_save_btn"]')
        element10.click()
        sql = "select * from bankcard where cardBank='伊拉克国民银行' and userID=1"
        if len(DBUtils.select(sql, [])) == 1:
            print("新增失败")
            self.book.get_sheet(3).write(6, 6, "passed")
            self.book.get_sheet(3).write(6, 7, "晏凯旋")
            self.book.get_sheet(3).write(6, 8, self.time)
            self.book.save("b.xls")
        else:
            print("新增成功")
            self.book.get_sheet(3).write(6, 6, "failed")
            self.book.get_sheet(3).write(6, 7, "晏凯旋")
            self.book.get_sheet(3).write(6, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 1

    def test_delete_success(self):  # 删除成功
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击删除
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[3]/div/div/div[1]/ul/li/button[2]')
        element06.click()
        time.sleep(1)
        # 点击确认
        element07 = driver.find_element(By.XPATH,
                                        '/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[1]')
        element07.click()

        sql = "select * from bankcard where cardNum=0123456789665599"
        if len(DBUtils.select(sql, [])) == 0:
            print("删除成功")
            self.book.get_sheet(3).write(8, 6, "passed")
            self.book.get_sheet(3).write(8, 7, "晏凯旋")
            self.book.get_sheet(3).write(8, 8, self.time)
            self.book.save("b.xls")
        else:
            print("删除失败")
            self.book.get_sheet(3).write(8, 6, "failed")
            self.book.get_sheet(3).write(8, 7, "晏凯旋")
            self.book.get_sheet(3).write(8, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 0

    def test_delete_failed(self):  # 删除失败
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击删除
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[2]/div/div/div[1]/ul/li/button[2]')
        element06.click()
        time.sleep(1)
        # 点击取消
        element07 = driver.find_element(By.XPATH,
                                        '/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[2]')
        element07.click()
        # 点击确认
        time.sleep(1)
        element08 = driver.find_element(By.XPATH,
                                        '/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button')
        element08.click()

        sql = "select * from bankcard where cardNum=16598485765214999"
        if len(DBUtils.select(sql, [])) == 1:
            print("删除失败")
            self.book.get_sheet(3).write(9, 6, "passed")
            self.book.get_sheet(3).write(9, 7, "晏凯旋")
            self.book.get_sheet(3).write(9, 8, self.time)
            self.book.save("b.xls")
        else:
            print("删除成功")
            self.book.get_sheet(3).write(9, 6, "failed")
            self.book.get_sheet(3).write(9, 7, "晏凯旋")
            self.book.get_sheet(3).write(9, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 1

    def test_update_failed(self):  # 修改卡的名称
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击编辑
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[2]/div/div/div[1]/ul/li/button[1]')
        element06.click()
        time.sleep(1)
        # 输入卡名
        element07 = driver.find_element(By.XPATH,
                                        '//*[@id="cardBank_update_input"]')
        element07.send_keys("伊民民民银行")
        # 点击更新
        time.sleep(1)
        element08 = driver.find_element(By.XPATH,
                                        '//*[@id="bankCard_update_btn"]')
        element08.click()
        time.sleep(2)

        sql = "select * from bankcard where cardBank='伊民民民银行'"
        if len(DBUtils.select(sql, [])) == 0:
            print("修改失败")
            self.book.get_sheet(3).write(11, 6, "passed")
            self.book.get_sheet(3).write(11, 7, "晏凯旋")
            self.book.get_sheet(3).write(11, 8, self.time)
            self.book.save("b.xls")
        else:
            print("修改成功")
            self.book.get_sheet(3).write(11, 6, "failed")
            self.book.get_sheet(3).write(11, 7, "晏凯旋")
            self.book.get_sheet(3).write(11, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 0

    def test_update_suc(self):  # 修改卡的号 16位
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击编辑
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[2]/div/div/div[1]/ul/li/button[1]')
        element06.click()
        time.sleep(1)
        # 输入卡号
        element07 = driver.find_element(By.XPATH,
                                        '//*[@id="cardNum_update_input"]')
        element07.send_keys("0123456789885522")
        # 点击更新
        time.sleep(1)
        element08 = driver.find_element(By.XPATH,
                                        '//*[@id="bankCard_update_btn"]')
        element08.click()
        time.sleep(2)

        sql = "select * from bankcard where cardNum=0123456789885522"
        if len(DBUtils.select(sql, [])) == 1:
            print("修改成功")
            self.book.get_sheet(3).write(12, 6, "passed")
            self.book.get_sheet(3).write(12, 7, "晏凯旋")
            self.book.get_sheet(3).write(12, 8, self.time)
            self.book.save("b.xls")
        else:
            print("修改失败")
            self.book.get_sheet(3).write(12, 6, "failed")
            self.book.get_sheet(3).write(12, 7, "晏凯旋")
            self.book.get_sheet(3).write(12, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 1

    def test_update_failed008(self):  # 修改卡的号 17位
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击编辑
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[2]/div/div/div[1]/ul/li/button[1]')
        element06.click()
        time.sleep(1)
        # 输入卡号
        element07 = driver.find_element(By.XPATH,
                                        '//*[@id="cardNum_update_input"]')
        element07.send_keys("01234567898855220")
        # 点击更新
        time.sleep(1)
        element08 = driver.find_element(By.XPATH,
                                        '//*[@id="bankCard_update_btn"]')
        element08.click()
        time.sleep(2)

        sql = "select * from bankcard where cardNum=01234567898855220"
        if len(DBUtils.select(sql, [])) == 0:
            print("修改失败")
            self.book.get_sheet(3).write(13, 6, "passed")
            self.book.get_sheet(3).write(13, 7, "晏凯旋")
            self.book.get_sheet(3).write(13, 8, self.time)
            self.book.save("b.xls")
        else:
            print("修改成功")
            self.book.get_sheet(3).write(13, 6, "failed")
            self.book.get_sheet(3).write(13, 7, "晏凯旋")
            self.book.get_sheet(3).write(13, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 0

    def test_update_success008(self):  # 修改卡的类别
        driver = webdriver.Chrome()
        driver.maximize_window()
        driver.get("http://localhost:90")
        element01 = driver.find_element(By.XPATH,
                                        '//input[@type="text" and @placeholder="请输入您的用户名" and @class="form-control"]')
        element01.send_keys("lisi")
        element02 = driver.find_element(By.XPATH, '//input[@type="password" and @placeholder="请输入密码" ]')
        element02.send_keys("123456")
        element03 = driver.find_element(By.XPATH,
                                        '//button[@id="login_btn" and @class="btn btn-block btn-primary"]')
        element03.click()

        element04 = driver.find_element(By.XPATH, '//*[@id="leftbaraside"]/div[2]/nav/ul/li[4]/a')
        element04.click()
        time.sleep(1)
        element05 = driver.find_element(By.XPATH, '//a[@href="/user/personal/toBankCard.html"]')
        element05.click()
        time.sleep(1)
        # 点击编辑
        element06 = driver.find_element(By.XPATH,
                                        '/html/body/div/div/main/div/div/div[2]/div/div/div[1]/ul/li/button[1]')
        element06.click()
        time.sleep(1)
        # 输入卡号
        element07 = driver.find_element(By.XPATH,
                                        '//*[@id="bankCardUpdateModal"]/div/div/div[2]/form/div[2]/div[1]/div/label[1]/span')
        element07.click()
        # 点击更新
        time.sleep(1)
        element08 = driver.find_element(By.XPATH,
                                        '//*[@id="bankCard_update_btn"]')
        element08.click()
        time.sleep(2)

        sql = "select * from bankcard where id=7 and type=1"
        if len(DBUtils.select(sql, [])) == 1:
            print("修改成功")
            self.book.get_sheet(3).write(14, 6, "passed")
            self.book.get_sheet(3).write(14, 7, "晏凯旋")
            self.book.get_sheet(3).write(14, 8, self.time)
            self.book.save("b.xls")
        else:
            print("修改失败")
            self.book.get_sheet(3).write(14, 6, "failed")
            self.book.get_sheet(3).write(14, 7, "晏凯旋")
            self.book.get_sheet(3).write(14, 8, self.time)
            self.book.save("b.xls")
        driver.quit()
        assert len(DBUtils.select(sql, [])) == 1
